Plugins/Community Based Plugins/Microsoft Defender for Cloud Custom plugin Scenarios/AttackPathAnalysisWithMDC/AttackPathAnalysisWithMDC.yaml

Descriptor:
  Name: AttackPathAnalysis
  DisplayName: Attack Path Analysis by Defender for Cloud (Community release)
  Description:  KQL queries for retrieving the "Attack Paths" and related "Recommendations" identified by Defender CSPM 
    (Cloud Security Posture Management) in Microsoft Defender for Cloud.  
SkillGroups:
  - Format: KQL




    Skills:
      - Name: GetAttackPathsByNodeNames
        DisplayName: Get the Attack Paths including any of the cloud resources specified in input. 
        Description: Get a list with minimal info of the Attack Paths, in Defender for Cloud, including any of the cloud resources 
            (aka "nodes") specified in input as a comma-separated list of names.
        DescriptionForModel: Get a list with minimal info of the Attack Paths, in Defender for Cloud, including any of the cloud resources 
            (aka "nodes") specified in input as a comma-separated list of names.
            The Attack Paths are all and only those generated by Defender for Cloud within a specified time window, identified by an "end date" and a "number of 
            days" before that date. End date and number of days are also specified by the user as input. If not specified, it is assumed that 
            the search must be done considering the "end date" as today and the "number of days" as 1.
            The resources (node names) specified in input can be the names of any kind of cloud resources defined in Azure, AWS or GCP, like 
            computers, PODs in Kubernetes, container images, etc...
            For each Attack Paths the call returns its name (DisplayName), the date when it was first seen (FirstSeen), the date when it was last seen (LastActive) 
            and a 64-byte string representing a simplified version of its unique identifier (AttackPathSimpleId) 

            VERY IMPORTANT - Never truncate the list of Attack Paths retrieved by launching the KQL query related to this skill.
            If the set of results is too wide to be managed in the chat session, just invite the user to search for the Attack Paths and the 
            Reccomendations related to these cloud resources by searching directly in Defender for Cloud.
       
            
        Inputs:
          - Name: NodeNames
            Description: Provide a comma-separated list of the names of the nodes to be searched in the attack paths (e.g. VM123,podABC)
            Required: true          
          - Name: EndDate
            Description: Provide the end date (e.g. 2024-05-17T00:00:00Z). Default is today
            Required: false
          - Name: DaysBack
            Description: Provide the number of days to be considered in the past, starting from the end date (e.g. 2 for 2 days). Default is 1. 
            Required: false  
        Settings:
          Target: LogAnalytics
          TenantId: [Write your Tenant Id]
          SubscriptionId: [Write your Subscription Id]
          ResourceGroupName: [Write the name of your resource group]
          WorkspaceName: [Write the name of your Log Analytics or Sentinel workspace]      
          Template: |-
            let inputEndDate = "{{EndDate}}"; //Example-> "2024-05-17T00:00:00Z";
            let inputDaysBack = "{{DaysBack}}"; //Example-> "7";
            let inputNodeNames = "{{NodeNames}}"; //Example-> "VM123,podABC";                           
            let arrayOfNodeNames = split(tolower(inputNodeNames), ",");        
            let dateEnd = startofday( todatetime(iif( isempty(inputEndDate), tostring(now()), tostring(inputEndDate) )));
            let isInt = not(isnull(isnan(toreal(inputDaysBack))));
            let pastDays = toint(   
                iif(isInt and (inputDaysBack!=0),toint(inputDaysBack),1)
            );
            let dateStart = datetime_add('day',-pastDays,dateEnd);        
            let ap =
            (SecurityAttackPathData
            | where TimeGenerated >= dateStart and TimeGenerated < dateEnd
            | extend PathObject = parse_json(Path)
            | mvexpand PathObjectNode = PathObject.nodes
            | where isnotnull( PathObjectNode.cloudProvider) 
            | extend NodeDisplayName = tostring(PathObjectNode.displayName)
            | extend NodeFound = (tolower(NodeDisplayName) in (arrayOfNodeNames))
            | where NodeFound       
            | extend AttackPathSimpleId = substring(AttackPathId, strlen(AttackPathId) - 64)            
            | summarize FirstSeen=min(TimeGenerated), LastActive=max(TimeGenerated) by AttackPathSimpleId, DisplayName
            | order by DisplayName asc, LastActive desc
            );
            ap










      - Name: GetAttackPathsDetailsByAttackPathIDs
        DisplayName: Get the details of specific Attack Paths, in Defender for Cloud, selected by their simplified Attack Path ID
        Description: Get the details of specific Attack Paths identified by Defender for Cloud and selected by their simplified Attack Path ID (64-byte string).
          The simplified Attack Path IDs must be specified in input as a comma-separated list of 64-byte strings.
        DescriptionForModel: Get the details of specific Attack Paths identified by Defender for Cloud and selected by their simplified Attack Path ID (64-byte string).
          The simplified Attack Path IDs must be specified in input as a comma-separated list of 64-byte strings.
          The Attack Paths are further filtered as those generated by Defender for Cloud within a specified time window, identified by an "end date" and a "number of 
          days" before that date. End date and number of days are also specified by the user as input. If not specified, it is assumed that 
          the search must be done considering the "end date" as today and the "number of days" as 1.
            
          Each item returned by the call includes the following fields related to the Attack Paths.              
            entry point component node (EntryPointRef, the first node in the Attack Path, including its name, its cloud environment and its type), 
            target component node (TargetNodeRef, the last node in the Attack Path, including its name, its cloud environment and its type),  
            DisplayName, 
            Description, 
            RiskLevel and RsikLevelNum (with a numeric prefix per risk level), 
            today's status (IsActive), 
            number of components nodes included in the Attack Paths (AllNodesCount), 
            list of components (AllNodesDisplayNamesAndLabels, each component in the Attack Path, including its name, its cloud environment and its type), 
            risk factors (RiskFactors), 
            potential impact (PotentialImpact), 
            Mitre tactics and techniques (Mitre), 
            days of active duration (ActiveDurationDays which is the number of days between LastActive and FirstSeen), 
            first seen day (FirstSeen), 
            last seen day (LastActive), 
            number of occurrences within the specified time window (Occurrences),
            links to the remediation steps (RecommendationLinks), 
            today's status (IsActive).
          

        Inputs:
          - Name: AttackPathIds
            Description: Provide the comma separated value of the AttackPath IDs of interest (each should be 64 chars long)
            Required: true
          - Name: EndDate
            Description: Provide the end date (e.g. 2024-05-17T00:00:00Z). Default is today
            Required: false
          - Name: DaysBack
            Description: Provide the number of days to be considered in the past, starting from the end date (e.g. 2 for 2 days). Default is 1. 
            Required: false  
        Settings:
          Target: LogAnalytics
          TenantId: [Write your Tenant Id]
          SubscriptionId: [Write your Subscription Id]
          ResourceGroupName: [Write the name of your resource group]
          WorkspaceName: [Write the name of your Log Analytics or Sentinel workspace]   
          Template: |-
            let inputEndDate = "{{EndDate}}"; // Example-> "2024-05-17T00:00:00Z";
            let inputDaysBack = "{{DaysBack}}"; // Example-> "7";
            let inputAttackPathSimpleIds = "{{AttackPathIds}}"; //Comma-separated 
            let arrayOfAttackPathSimpleIds = split(toupper(inputAttackPathSimpleIds), ",");        
            let yesterday = startofday(ago(1d));           
            let dateEnd = startofday( todatetime(iif( isempty(inputEndDate), tostring(now()), tostring(inputEndDate) )));
            let isInt = not(isnull(isnan(toreal(inputDaysBack))));
            let pastDays = toint(   
                iif(isInt and (inputDaysBack!=0),toint(inputDaysBack),1)
            );
            let dateStart = datetime_add('day',-pastDays,dateEnd);
            let ap =
            (SecurityAttackPathData
            | where TimeGenerated >= dateStart and TimeGenerated < dateEnd
            | extend AttackPathSimpleId = substring(AttackPathId, strlen(AttackPathId) - 64)
            | where AttackPathSimpleId in (arrayOfAttackPathSimpleIds)
            | extend RiskFactors = tostring(RiskFactors)
            | extend PathObject = parse_json(Path)
            | mvexpand PathObjectNode = PathObject.nodes
            | where isnotnull( PathObjectNode.cloudProvider) 
            | extend NodeDisplayName = tostring(PathObjectNode.displayName)
            | extend NodeCloudProvider = tostring(PathObjectNode.cloudProvider)
            | extend NodeLabel = tostring(PathObjectNode.label)
            | extend Assessments = tostring(Assessments)
            | extend NodeDisplayNameAndLabel = strcat(NodeDisplayName, " (", iif(NodeCloudProvider!="General",strcat(NodeCloudProvider,"-"),""), NodeLabel, ")")
            | order by NodeDisplayNameAndLabel
            | summarize AllNodesDisplayNamesAndLabels = make_list(NodeDisplayNameAndLabel) by TimeGenerated, tostring(PathObject), AttackPathId, DisplayName, Description, RiskLevel, RiskFactors, PotentialImpact, Mitre, AdditionalRemediationSteps, EntrypointId, TargetId, Assessments
            | extend AllNodesDisplayNamesAndLabels = tostring(AllNodesDisplayNamesAndLabels)
            | extend AllNodesCount = strlen(AllNodesDisplayNamesAndLabels) - strlen(replace(",", "", AllNodesDisplayNamesAndLabels)) +1
            | extend PathObject = parse_json(PathObject)
            | mvexpand PathObjectNode = PathObject.nodes
            | extend EntryNodeDisplayName = iif(PathObjectNode.mapIdentifier == EntrypointId,tostring(PathObjectNode.displayName),"")
            | extend EntryNodeCloudProvider = iif(PathObjectNode.mapIdentifier == EntrypointId,tostring(PathObjectNode.cloudProvider),"")
            | extend EntryNodeLabel = iif(PathObjectNode.mapIdentifier == EntrypointId,tostring(PathObjectNode.label),"")
            | extend EntryNodeDisplayNameAndLabel = strcat(EntryNodeDisplayName," (",EntryNodeCloudProvider,"-",EntryNodeLabel,")")
            | extend TargetNodeDisplayName = iif(PathObjectNode.mapIdentifier == TargetId,tostring(PathObjectNode.displayName),"")
            | extend TargetNodeCloudProvider = iif(PathObjectNode.mapIdentifier == TargetId,tostring(PathObjectNode.cloudProvider),"")
            | extend TargetNodeLabel = iif(PathObjectNode.mapIdentifier == TargetId,tostring(PathObjectNode.label),"")
            | extend TargetNodeDisplayNameAndLabel = strcat(TargetNodeDisplayName," (",TargetNodeCloudProvider,"-",TargetNodeLabel,")")
            | summarize EntryNodeRef = max(EntryNodeDisplayNameAndLabel), TargetNodeRef=max(TargetNodeDisplayNameAndLabel) by TimeGenerated, DisplayName, Description, RiskLevel, AllNodesCount, AllNodesDisplayNamesAndLabels, tostring(RiskFactors), PotentialImpact, Mitre, AdditionalRemediationSteps, Assessments
            | mvexpand AssessmentPath = parse_json(Assessments)
            | extend AssessmentPath_start_index = indexof(AssessmentPath, "/assessments/") + strlen("/assessments/")
            | extend AssessmentPath_end_index = strlen(AssessmentPath)
            | extend AssessmentPath_substring = substring(AssessmentPath, AssessmentPath_start_index, AssessmentPath_end_index - AssessmentPath_start_index)
            | extend AssessmentPath_split_string = split(AssessmentPath_substring, '/')
            | extend AssessmentId = tostring(AssessmentPath_split_string[0])
            | extend parts = split(AssessmentPath, "/")
            | extend AssessedResourceId = tostring(split(AssessmentPath,"/providers/Microsoft.Security/assessments/")[0])
            | join kind=leftouter SecurityRecommendation on $left.AssessmentId == $right.RecommendationId and $left.AssessedResourceId == $right.AssessedResourceId
            | where RecommendationDisplayName != ""
            | extend RecommendationLink = iif(RecommendationLink!="", strcat("https://",RecommendationLink),"")
            | extend RecommendationDisplayNameAndLink = strcat(RecommendationDisplayName,",", RecommendationLink)
            | summarize FirstSeenPar=min(TimeGenerated), LastActivePar=max(TimeGenerated), OccurrencesPar=count() by EntryNodeRef, TargetNodeRef, DisplayName, Description, RiskLevel, AllNodesCount, AllNodesDisplayNamesAndLabels, tostring(RiskFactors), PotentialImpact, Mitre, AdditionalRemediationSteps, RecommendationSeverity, RecommendationDisplayNameAndLink 
            | extend ActiveDurationDays = datetime_diff('day', LastActivePar, FirstSeenPar) + 1
            | extend SevNum = case( RiskLevel == "Critical", 0, RiskLevel == "High", 1, RiskLevel == "Medium", 2, RiskLevel == "Low", 3, 10)
            | extend RiskLevelNum = strcat(tostring(SevNum),"-",RiskLevel)
            | summarize FirstSeen=min(FirstSeenPar), LastActive=max(LastActivePar), Occurrences=sum(OccurrencesPar), RecommendationLinks = make_list(RecommendationDisplayNameAndLink) by EntryNodeRef, TargetNodeRef, DisplayName, Description, RiskLevel, RiskLevelNum, AllNodesCount, AllNodesDisplayNamesAndLabels, tostring(RiskFactors), PotentialImpact, Mitre, AdditionalRemediationSteps , ActiveDurationDays
            | extend IsActive = iff(LastActive > yesterday, "Yes", "No")
            | order by RiskLevelNum asc 
            );
            ap







  


      - Name: GetRecommendationsByAttackPathIDs
        DisplayName: Get the Recommendations releated to the Attack Paths selected by the Attack Path IDs specified in input.  
        Description: Get info about the Recommendations, in Defender for Cloud, releated to the Attack Paths selected by the Attack Path IDs specified in input.  
        DescriptionForModel: Get info about the Recommendations, in Defender for Cloud, releated to the Attack Paths selected by the Attack Path IDs specified in input.   
            The Recommendations are all and only those generated within a specified time window, identified by an "end date" and a "number of 
            days" before that date. End date and number of days are also specified by the user as input. If not specified, it is assumed that 
            the search must be done considering the "end date" as today and the "number of days" as 1.

            For each Recommendation the call returns the link to the page, in the Azure portal, describing in full details the Recommendation itself.
            It also returns the source (cloud environment hosting the resource related to this recommendation) and the name and the type of the cloud resource 
            that is targeted by the recommendation. 

            VERY IMPORTANT - Never truncate the list of Recommendations retrieved by launching the KQL query related to this skill.
            If the set of results is too wide to be managed in the chat session, just invite the user to search for the Attack Paths and the 
            Reccomendations related to these cloud resources by searching directly in Defender for Cloud.

        Inputs:
          - Name: AttackPathIds
            Description: Provide the comma separated value of the AttackPath IDs of interest (each should be 64 chars long)
            Required: true
          - Name: EndDate
            Description: Provide the end date (e.g. 2024-05-17T00:00:00Z). Default is today
            Required: false
          - Name: DaysBack
            Description: Provide the number of days to be considered in the past, starting from the end date (e.g. 2 for 2 days). Default is 1. 
            Required: false  
        Settings:
          Target: LogAnalytics
          TenantId: [Write your Tenant Id]
          SubscriptionId: [Write your Subscription Id]
          ResourceGroupName: [Write the name of your resource group]
          WorkspaceName: [Write the name of your Log Analytics or Sentinel workspace]     
          Template: |-
            let inputEndDate = "{{EndDate}}"; // Example-> "2024-05-17T00:00:00Z"; 
            let inputDaysBack = "{{DaysBack}}"; // Example-> "7";
            let inputAttackPathSimpleIds = "{{AttackPathIds}}";         
            let arrayOfAttackPathSimpleIds = split(toupper(inputAttackPathSimpleIds), ",");        
            let yesterday = startofday(ago(1d));           
            let dateEnd = startofday( todatetime(iif( isempty(inputEndDate), tostring(now()), tostring(inputEndDate) )));
            let isInt = not(isnull(isnan(toreal(inputDaysBack))));
            let pastDays = toint(   
                iif(isInt and (inputDaysBack!=0),toint(inputDaysBack),1)
            );
            let dateStart = datetime_add('day',-pastDays,dateEnd);
            let ap =
            (SecurityAttackPathData
            | where TimeGenerated >= dateStart and TimeGenerated < dateEnd
            | extend AttackPathSimpleId = substring(AttackPathId, strlen(AttackPathId) - 64)
            | where AttackPathSimpleId in (arrayOfAttackPathSimpleIds)
            | extend RiskFactors = tostring(RiskFactors)
            | extend PathObject = parse_json(Path)
            | mvexpand PathObjectNode = PathObject.nodes
            | extend NodeDisplayName = tostring(PathObjectNode.displayName)
            | extend NodeCloudProvider = tostring(PathObjectNode.cloudProvider)
            | extend NodeLabel = tostring(PathObjectNode.label)
            | extend Assessments = tostring(Assessments)
            | extend PathObject = parse_json(PathObject)
            | mvexpand PathObjectNode = PathObject.nodes
            | extend EntryNodeDisplayName = iif(PathObjectNode.mapIdentifier == EntrypointId,tostring(PathObjectNode.displayName),"")
            | extend EntryNodeCloudProvider = iif(PathObjectNode.mapIdentifier == EntrypointId,tostring(PathObjectNode.cloudProvider),"")
            | extend EntryNodeLabel = iif(PathObjectNode.mapIdentifier == EntrypointId,tostring(PathObjectNode.label),"")
            | extend EntryNodeDisplayNameAndLabel = strcat(EntryNodeDisplayName," (",EntryNodeCloudProvider,"-",EntryNodeLabel,")")
            | extend TargetNodeDisplayName = iif(PathObjectNode.mapIdentifier == TargetId,tostring(PathObjectNode.displayName),"")
            | extend TargetNodeCloudProvider = iif(PathObjectNode.mapIdentifier == TargetId,tostring(PathObjectNode.cloudProvider),"")
            | where TargetNodeCloudProvider != ""
            | extend TargetNodeLabel = iif(PathObjectNode.mapIdentifier == TargetId,tostring(PathObjectNode.label),"")
            | extend TargetNodeDisplayNameAndLabel = strcat(TargetNodeDisplayName," (",TargetNodeCloudProvider,"-",TargetNodeLabel,")")
            | mvexpand AssessmentPath = parse_json(Assessments)
            | extend AssessmentPath_start_index = indexof(AssessmentPath, "/assessments/") + strlen("/assessments/")
            | extend AssessmentPath_end_index = strlen(AssessmentPath)
            | extend AssessmentPath_substring = substring(AssessmentPath, AssessmentPath_start_index, AssessmentPath_end_index - AssessmentPath_start_index)
            | extend AssessmentPath_split_string = split(AssessmentPath_substring, '/')
            | extend AssessmentId = tostring(AssessmentPath_split_string[0])
            | extend parts = split(AssessmentPath, "/")
            | extend AssessedResourceId = tostring(split(AssessmentPath,"/providers/Microsoft.Security/assessments/")[0])
            | extend AssessedResourceName = tostring(split(AssessedResourceId,"/")[array_length(split(AssessedResourceId,"/"))-1])
            | join kind=leftouter SecurityRecommendation on $left.AssessmentId == $right.RecommendationId and $left.AssessedResourceId == $right.AssessedResourceId
            | where RecommendationDisplayName != "" and RecommendationState == "Unhealthy"
            | extend ResourceSource = tostring(parse_json(Properties).resourceDetails.source)
            | extend ResourceName = tostring(parse_json(Properties).resourceDetails.resourceName)
            | extend ResourceType = tostring(parse_json(Properties).resourceDetails.resourceType)
            | extend RecommendationSevNum = case( RiskLevel == "Critical", 0, RiskLevel == "High", 1, RiskLevel == "Medium", 2, RiskLevel == "Low", 3, 10)
            | extend RecommendationLink = iif(RecommendationLink!="", strcat("https://",RecommendationLink),"")
            | distinct  RecommendationSevNum, RecommendationDisplayName, RecommendationLink, ResourceSource, ResourceName, ResourceType
            | order by RecommendationSevNum, RecommendationDisplayName
            | project-away RecommendationSevNum
            );
            ap










  


      - Name: GetRecommendationsByNodeNames
        DisplayName: Get the Recommendations releated to the Attack Paths including any of the cloud resources specified in input. 
        Description: Get info about the Recommendations, in Defender for Cloud, releated to the Attack Paths including any of the cloud resources 
            (aka "nodes") specified in input as a comma-separated list of names.  
        DescriptionForModel: Get info about the Recommendations, in Defender for Cloud, releated to the Attack Paths including any of the cloud resources 
            (aka "nodes") specified in input as a comma-separated list of names. 
            The Recommendations are all and only those generated within a specified time window, identified by an "end date" and a "number of 
            days" before that date. End date and number of days are also specified by the user as input. If not specified, it is assumed that 
            the search must be done considering the "end date" as today and the "number of days" as 1.
            The resources (node names) specified in input can be the names of any kind of cloud resources defined in Azure, AWS or GCP, like 
            computers, PODs in Kubernetes, container images, etc...
            For each Recommendation the call returns the link to the page, in the Azure portal, describing in full details the Recommendation itself.
            It also returns the source (cloud environment hosting the resource related to this recommendation) and the name and the type of the cloud resource 
            that is targeted by the recommendation. 

            VERY IMPORTANT - Never truncate the list of Recommendations retrieved by launching the KQL query related to this skill.
            If the set of results is too wide to be managed in the chat session, just invite the user to search for the Attack Paths and the 
            Reccomendations related to these cloud resources by searching directly in Defender for Cloud.

        Inputs:
          - Name: NodeNames
            Description: Provide a comma-separated list of the names of the nodes to be searched in the attack paths (e.g. VM123,podABC)
            Required: true
          - Name: EndDate
            Description: Provide the end date (e.g. 2024-05-17T00:00:00Z). Default is today
            Required: false
          - Name: DaysBack
            Description: Provide the number of days to be considered in the past, starting from the end date (e.g. 2 for 2 days). Default is 1. 
            Required: false  
        Settings:
          Target: LogAnalytics
          TenantId: [Write your Tenant Id]
          SubscriptionId: [Write your Subscription Id]
          ResourceGroupName: [Write the name of your resource group]
          WorkspaceName: [Write the name of your Log Analytics or Sentinel workspace]     
          Template: |-
            let inputEndDate = "{{EndDate}}"; //Example-> "2024-05-17T00:00:00Z";
            let inputDaysBack = "{{DaysBack}}"; //Example-> "7";
            let inputNodeNames = "{{NodeNames}}"; //Example-> "VM123,podABC";                                              
            let arrayOfNodeNames = split(tolower(inputNodeNames), ",");        
            let yesterday = startofday(ago(1d));           
            let dateEnd = startofday( todatetime(iif( isempty(inputEndDate), tostring(now()), tostring(inputEndDate) )));
            let isInt = not(isnull(isnan(toreal(inputDaysBack))));
            let pastDays = toint(   
                iif(isInt and (inputDaysBack!=0),toint(inputDaysBack),1)
            );
            let dateStart = datetime_add('day',-pastDays,dateEnd);
            let ap =
            (SecurityAttackPathData
            | where TimeGenerated >= dateStart and TimeGenerated < dateEnd
            | extend AttackPathSimpleId = substring(AttackPathId, strlen(AttackPathId) - 64)
            | extend RiskFactors = tostring(RiskFactors)
            | extend PathObject = parse_json(Path)
            | mvexpand PathObjectNode = PathObject.nodes
            | extend NodeDisplayName = tostring(PathObjectNode.displayName)
            | extend NodeFound = (tolower(NodeDisplayName) in (arrayOfNodeNames))
            | where NodeFound       
            | extend NodeCloudProvider = tostring(PathObjectNode.cloudProvider)
            | extend NodeLabel = tostring(PathObjectNode.label)
            | extend Assessments = tostring(Assessments)
            | extend PathObject = parse_json(PathObject)
            | mvexpand PathObjectNode = PathObject.nodes
            | extend EntryNodeDisplayName = iif(PathObjectNode.mapIdentifier == EntrypointId,tostring(PathObjectNode.displayName),"")
            | extend EntryNodeCloudProvider = iif(PathObjectNode.mapIdentifier == EntrypointId,tostring(PathObjectNode.cloudProvider),"")
            | extend EntryNodeLabel = iif(PathObjectNode.mapIdentifier == EntrypointId,tostring(PathObjectNode.label),"")
            | extend EntryNodeDisplayNameAndLabel = strcat(EntryNodeDisplayName," (",EntryNodeCloudProvider,"-",EntryNodeLabel,")")
            | extend TargetNodeDisplayName = iif(PathObjectNode.mapIdentifier == TargetId,tostring(PathObjectNode.displayName),"")
            | extend TargetNodeCloudProvider = iif(PathObjectNode.mapIdentifier == TargetId,tostring(PathObjectNode.cloudProvider),"")
            | extend TargetNodeLabel = iif(PathObjectNode.mapIdentifier == TargetId,tostring(PathObjectNode.label),"")
            | extend TargetNodeDisplayNameAndLabel = strcat(TargetNodeDisplayName," (",TargetNodeCloudProvider,"-",TargetNodeLabel,")")
            | mvexpand AssessmentPath = parse_json(Assessments)
            | extend AssessmentPath_start_index = indexof(AssessmentPath, "/assessments/") + strlen("/assessments/")
            | extend AssessmentPath_end_index = strlen(AssessmentPath)
            | extend AssessmentPath_substring = substring(AssessmentPath, AssessmentPath_start_index, AssessmentPath_end_index - AssessmentPath_start_index)
            | extend AssessmentPath_split_string = split(AssessmentPath_substring, '/')
            | extend AssessmentId = tostring(AssessmentPath_split_string[0])
            | extend parts = split(AssessmentPath, "/")
            | extend AssessedResourceId = tostring(split(AssessmentPath,"/providers/Microsoft.Security/assessments/")[0])
            | extend AssessedResourceName = tostring(split(AssessedResourceId,"/")[array_length(split(AssessedResourceId,"/"))-1])
            | join kind=leftouter SecurityRecommendation on $left.AssessmentId == $right.RecommendationId and $left.AssessedResourceId == $right.AssessedResourceId
            | where RecommendationDisplayName != "" and RecommendationState == "Unhealthy"
            | extend ResourceSource = tostring(parse_json(Properties).resourceDetails.source)
            | extend ResourceName = tostring(parse_json(Properties).resourceDetails.resourceName)
            | extend ResourceType = tostring(parse_json(Properties).resourceDetails.resourceType)
            | extend ResourceType = iif(ResourceType == ".containerimage", "containerImage", ResourceType)
            | extend RecommendationSevNum = case( RiskLevel == "Critical", 0, RiskLevel == "High", 1, RiskLevel == "Medium", 2, RiskLevel == "Low", 3, 10)
            | extend RecommendationLink = iif(RecommendationLink!="", strcat("https://",RecommendationLink),"")
            | distinct  RecommendationSevNum, RecommendationDisplayName, RecommendationLink, ResourceSource, ResourceName, ResourceType
            | order by RecommendationSevNum, RecommendationDisplayName
            | project-away RecommendationSevNum
            );
            ap